<?php

/*

	<<dbTools class version 0.2a>>
	
	Written by Andrew Kongs - 2008
	
	List of function:
	
		function: selectRow($table, $key, $keyValue)
		
		function: checkForRow($table, $key, $keyValue)
	
		function: selectRand($table)

		function: selectRandWithKey($table, $key, $keyValue)

		function: insert($table, $array)

		function: update($table, $whereKey, $array)
	
		function: drop($tableName, $key, $keyValue)
		
		function: selectMultiple($table, $key, $keyValue)
		
		function: tableAsArray($table)
		
		function: customQuery($query)

*/

class dbTools
{
	private $settings = array();
	
	function __construct($mysql_server, $username, $dbName, $passwd)
	{
	   $this->settings['mysql_server'] = $mysql_server;
	   $this->settings['username'] = $username;
	   $this->settings['dbName'] = $dbName;
	   $this->settings['passwd'] = $passwd;
	}
	
	/*
		function: selectRow($table, $key, $keyValue)
		
		Selects a single record from the table given in the first arguments based on the column 
		specified by the key in the second argument with the value of the third argument. Here is 
		an example query where the arguments are ('users', 'username', 'lexxmac'):
		
		SELECT *  FROM `users` WHERE `username` = 'lexxmac' LIMIT 0 , 1
		
		The returned item is an associative array keyed with column names and values from the row 
		that result produced. Should there not be a result, the function returns false.
		
	*/
	public function selectRow($table, $key, $keyValue)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// setting up the SQL query, properly escaping the input to prevent injection
		
		if (gettype($keyValue) == "integer")
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) 
				. "` = " . mysql_real_escape_string($keyValue, $link) . " LIMIT 0,1";
		}
		else
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) 
				. "` = '" . mysql_real_escape_string($keyValue, $link) . "' LIMIT 0,1";
		}
		
		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		// grab the result's  output from MySQL
		$line = mysql_fetch_array($result, MYSQL_ASSOC);
		
		if ($line != '')
		{	foreach ($line as $key => $value)
			{
				$returnMe[$key] =  $value;
			}
		}
		else //return false if there is no result
		{
			$returnMe = false;
		}
		
		
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		return $returnMe;
	}
	
	/*
		function: checkForRow($table, $key, $keyValue)
		
		Selects a single record from the table given in the first arguments based on the column 
		specified by the key in the second argument with the value of the third argument. Here is 
		an example query where the arguments are ('users', 'username', 'lexxmac'):
		
		SELECT *  FROM `users` WHERE `username` = 'lexxmac' LIMIT 0 , 1
		
		The returned item is a simple boolean, true if found, false if not found.
		
	*/
	function checkForRow($table, $key, $keyValue)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// setting up the SQL query, properly escaping the input to prevent injection
		
		if (gettype($keyValue) == "integer")
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . 
			mysql_real_escape_string($key, $link) . "` = " . mysql_real_escape_string($keyValue, $link);
		}
		else
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . 
			mysql_real_escape_string($key, $link) . "` = '" . mysql_real_escape_string($keyValue, $link) . "'" ;	
		}

		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		// grab the result's  output from MySQL
		$line = mysql_fetch_array($result, MYSQL_ASSOC) ;
		
		if ($line == '')//Returns false if the result array is empty
		{
			$returnMe = false;
		} else {
			$returnMe = true;
		}
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		return $returnMe;
	}

	/*
		function: selectRand($table)
		
		Selects a random row from the table, returns the row as an associative array. 
		
	*/
	function selectRand($table)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// Performing SQL query
		$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` ORDER BY RAND() LIMIT 0,1" ;
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		$line = mysql_fetch_array($result, MYSQL_ASSOC) ;
		
		if ($line == '')
		{
			
		} else {
			foreach ($line as $key => $value)
			{
				$returnMe[$key] =  $value;
			}
		}
		
		//Returns false if the result array is empty
		if ($returnMe == null)
		{
			$returnMe = false;
		}
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		return $returnMe;
	}
	
	
	/*
		function: selectRandWithKey($table, $key, $keyValue)
		
		Does the exact same thing as selectRand, but allows you specify a key
		
		
	*/
	function selectRandWithKey($table, $key, $keyValue)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// Performing SQL query
		
		
		if (gettype($keyValue) == "integer")
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) . 
			"` = " . mysql_real_escape_string($keyValue, $link) . " ORDER BY RAND() LIMIT 0,1" ;
		}
		else
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) . 
			"` = '" . mysql_real_escape_string($keyValue, $link) . "' ORDER BY RAND() LIMIT 0,1" ;
		}
		
		
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		$line = mysql_fetch_array($result, MYSQL_ASSOC) ;
		
		if ($line == '')
		{
			$returnMe = false;//Returns false if the result array is empty
		} else {
			foreach ($line as $key => $value)
			{
				$returnMe[$key] =  $value;
			}
		}
		
		
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		return $returnMe;
	}
	
	/*
		function: insert($table, $array)
		
		inserts an array in to the database given the tablename and an associative array with the 
		same keys as the column titles
		
		
	*/
	
	function insert($table, $array)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
	
		$query = 'INSERT INTO `' . mysql_real_escape_string($table, $link) . '` (';	
		
		$counter;
		
		//pass MySQL the names of the cells that are being inserted
		foreach ($array as $key => $value)
		{
			if ($counter == (count($array) - 1))
			{
				$query = $query . ' `' . mysql_real_escape_string($key, $link) .'`)  ';
			} else {
				$query = $query . ' `' . mysql_real_escape_string($key, $link) .'`,  ';
			}
			$counter++;
		}
		
		$query = $query . " VALUES (";
		$counter = 0;
		
		// pass MySQL the value of each element being inserted
		foreach ($array as $key => $value)
		{
			if ($counter == (count($array) - 1))
			{
				if (gettype($keyValue) == "integer")
				{
					$query = $query . " " . mysql_real_escape_string($value, $link) . " );";
				}
				else
				{
					$query = $query . " '" . mysql_real_escape_string($value, $link) . "' );";
				}
			} 
			else 
			{
				if (gettype($keyValue) == "integer")
				{
					$query = $query . " " . mysql_real_escape_string($value, $link) .", ";
				}
				else
				{
					$query = $query . " '" . mysql_real_escape_string($value, $link) ."', ";
				}		
			}
			$counter++;
		}
	
		// Performing SQL query
		
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());
		
		// gets the row id of the inserted row
		$id = -1;
		if($result == 1)
			$id = mysql_query("SELECT LAST_INSERT_ID()");
		if($id != 0)
			$result = $id;
		
		//print ("DEBUG## " . $query);
		
		// Closing connection
		mysql_close($link);
		
		return $result;
	}
	
	/*
		function: update($table, $array)
		
		updates an entry that already exists, works on the same principle as insert
		
		
	*/
	function update($table, $whereKey, $array)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		//UPDATE `toy`.`stuff` SET `numba` = '203944293', `stringy` = 'mushi-mushi' WHERE `stuff`.`id` =2 LIMIT 1;
		
		$query = 'UPDATE `' . $table . '` SET ';

		$counter = 0;

		//assemble the array of values into an SQL query		
		foreach ($array as $key => $value)
		{
			if ($counter == (count($array) - 1))
			{
				$query .= '`' . $key . '`' . ' = \'' . $value . '\' ';
			}
			else
			{
				$query .= '`' . $key . '`' . ' = \'' . $value . '\', ';
			}
			
			$counter++;
		}
		
		if (is_numeric($whereKey))
		{
			 $query .= " WHERE `" . $whereKey . "` = " . $array[$whereKey] . ' LIMIT 1;';
		}
		else
		{
			 $query .= " WHERE `" . $whereKey . "` = '" . $array[$whereKey] . '\' LIMIT 1;';
		}
		
		
		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());
		// Closing connection
		mysql_close($link);
		//return out
		return $returnMe;
	} 
	
	/*
		function: drop($tableName, $key, $keyValue)
		
		Simply drops the entry with the given key.  This CAN delete multiple entries with one call
		
	*/
	function drop($table, $key, $keyValue)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
	
		// Performing SQL query	
		if (gettype($keyValue) == "integer")
		{
			$query = "DELETE FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . 
			mysql_real_escape_string($key, $link) . "` = " . mysql_real_escape_string($keyValue, $link) ;
		}
		else
		{
			$query = "DELETE FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . 
			mysql_real_escape_string($key, $link) . "` = '" . mysql_real_escape_string($keyValue, $link) . "'";
		}
		
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());
		
		// Closing connection
		mysql_close($link);
		
		return $result;
	}
	
	
	/*
		function: selectMultiple($table, $key, $keyValue)
		
		Just like select, but allows more than a single result and returns an array of result rows
		
		
	*/
	
	function selectMultiple($table, $key, $keyValue)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// setting up the SQL query, properly escaping the input to prevent injection
		if (gettype($keyValue) == "integer")
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) 
				. "` = " . mysql_real_escape_string($keyValue, $link) ;
		}
		else
		{
			$query = "SELECT * FROM `" . mysql_real_escape_string($table, $link) . "` WHERE `" . mysql_real_escape_string($key, $link) 
				. "` = \"" . mysql_real_escape_string($keyValue, $link) . '"';
		}
		
		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		$counter = 0;
		
		// grab the result's  output from MySQL
		while($line = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			//ensure the result is not empty before assigning
			if ($line != '')
			{	
				//copy row elements to output array
 				foreach ($line as $token => $value) 
   				{
       				$returnMe[$counter][$token] = $line[$token];
   				}
			}
			$counter++;
		}
		
		if ($returnMe == null)
		{
			$returnMe = false;
		}
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		//Returns false if the result array is empty
		if ($returnMe == null)
		{
			$returnMe = false;
		}
		
		return $returnMe;
	}
	
	/*
		function: tableAsArray($table)
		
		This function returns an entire table as an associative array.
		
	*/
	function tableAsArray($table)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		// setting up the SQL query, properly escaping the input to prevent injection
		$query = 'SELECT * FROM ' . mysql_real_escape_string($table, $link);
		
		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		$counter = 0;
		
		// grab the result's  output from MySQL
		while($line = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			//ensure the result is not empty before assigning
			if ($line != '')
			{	
				//copy row elements to output array
 				foreach ($line as $token => $value) 
   				{
       				$returnMe[$counter][$token] = $line[$token];
   				}
			}
			$counter++;
		}
		
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		//Returns false if the result array is empty
		if ($returnMe == null)
		{
			$returnMe = false;
		}
		
		return $returnMe;
	}
	
	/*
		function: customQuery($query)
		
		This function returns results of the input query as an associative array. 
		USE WITH CAUTION!!! No injection protection is built-in
		
	*/
	function customQuery($query)
	{
		//Instantiate array to return
		$returnMe = array();
		
		$link = mysql_connect($this->settings['mysql_server'], $this->settings['username'], $this->settings['passwd']) or 
			die('Could not connect: ' . mysql_error());
			
		//Select the database
		mysql_select_db($this->settings['dbName']) or die('Could not select database' . $this->settings['dbName']);
		
		//perform the query itself
		$result = mysql_query($query) or die('Query failed: ' . mysql_error());

		$counter = 0;
		
		// grab the result's  output from MySQL
		while($line = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			//ensure the result is not empty before assigning
			if ($line != '')
			{	
				//copy row elements to output array
 				foreach ($line as $token => $value) 
   				{
       				$returnMe[$counter][$token] = $line[$token];
   				}
			}
			$counter++;
		}
		
		
		// Free resultset
		mysql_free_result($result);

		// Closing connection
		mysql_close($link);
		
		//returns the boolean value for query execution if the results are null
		if ($returnMe == null)
		{
			$returnMe = $result;
		}
		
		return $returnMe;
	}
}

?>